package co.gov.medellin.ssti.sub.model.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import co.gov.medellin.ssti.sub.common.ConexionFactory;
import co.gov.medellin.ssti.sub.common.excepcion.SUBException;
import co.gov.medellin.ssti.sub.model.dao.ISecretariaDAO;

import com.cs.beneficios.business.model.AreaVO;

public class SecretariaDAOImpl extends BaseDAOImpl implements ISecretariaDAO {

	// **************************************
	// QUERYS
	// **************************************
	private static final String SELECT_TODAS_SECRETARIAS = "SELECT AREA.CODIGO CODIGO, AREA.NOMBRE NOMBRE "
			+ "FROM TCS_BEN_AREA AREA WHERE AREA.AREA_CODIGO IS NULL "
			+ "ORDER BY AREA.NOMBRE";
	
	private static final String SELECT_BY_USER = "SELECT A.CODIGO CODIGO, A.NOMBRE NOMBRE " +
			" FROM TCS_BEN_AREA A WHERE A.CODIGO = ANY(SELECT PRO.AREA_CODIGO " +
			"FROM TCS_BEN_PROGRAMA PRO WHERE (PRO.VIGENCIA = 'A' OR SYSDATE BETWEEN PRO.DESDE_VIGENCIA AND  PRO.HASTA_VIGENCIA) " +
			"AND PRO.USU_CODIGO = ? " + //PARAMETRO
			"UNION SELECT AREA.AREA_CODIGO " +
			"FROM TCS_BEN_SUBPROGRAMA SUB, TCS_BEN_AREA AREA " +
			"WHERE SUB.AREA_CODIGO = AREA.CODIGO " +
			"AND (SUB.VIGENCIA = 'A' OR SYSDATE BETWEEN SUB.DESDE_VIGENCIA AND  SUB.HASTA_VIGENCIA) " +
			"AND SUB.USU_CODIGO = ? " + //PARAMETRO
			"UNION SELECT CAMPO_N1 FROM PAOYER.TCS_USUARIO " +
			"WHERE CAMPO_N1 IS NOT NULL AND CODIGO = ? " +
			"UNION  SELECT AREA.AREA_CODIGO " + //PARAMETRO
			"FROM TCS_BEN_SUBPROGRAMA_USUARIO SUBUS, TCS_BEN_SUBPROGRAMA SUB, TCS_BEN_AREA AREA " +
			"WHERE SUBUS.SBPR_CODIGO = SUB.CODIGO AND SUB.AREA_CODIGO = AREA.CODIGO " +
			"AND (SUB.VIGENCIA = 'A' OR SYSDATE BETWEEN SUB.DESDE_VIGENCIA AND  SUB.HASTA_VIGENCIA) " +
			"AND SUBUS.USU_CODIGO = ? ) ORDER BY A.NOMBRE"; //PARAMETRO

	/**
	 * 
	 */
	public List loadAllSecretarias() {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		List secretarias = new ArrayList();

		try {
			con = ConexionFactory.getInstance().getConnection();
			pstmt = con.prepareStatement(SELECT_TODAS_SECRETARIAS);
			rst = pstmt.executeQuery();
			while (rst.next()) {
				AreaVO secretaria = new AreaVO();
				secretaria.setCodearea(rst.getString("CODIGO"));
				secretaria.setNombre(rst.getString("NOMBRE"));
				secretarias.add(secretaria);
			}
		} catch (SQLException e) {
			SUBException
					.logDebugError(
							"Error inesperado obteniendo la lista de todas las secretarias.",
							e);
		} finally {
			close(con, pstmt, rst);
		}
		return secretarias;
	}
	
	/**
	 * 
	 */
	public List loadSecretariasByUser(String strCodeUsu){
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		List secretarias = new ArrayList();

		try {
			con = ConexionFactory.getInstance().getConnection();
			pstmt = con.prepareStatement(SELECT_BY_USER);
			pstmt.setString(1, strCodeUsu);
			pstmt.setString(2, strCodeUsu);
			pstmt.setString(3, strCodeUsu);
			pstmt.setString(4, strCodeUsu);
			rst = pstmt.executeQuery();
			while (rst.next()) {
				AreaVO secretaria = new AreaVO();
				secretaria.setCodearea(rst.getString("CODIGO"));
				secretaria.setNombre(rst.getString("NOMBRE"));
				secretarias.add(secretaria);
			}
		} catch (SQLException e) {
			SUBException
					.logDebugError(
							"Error inesperado obteniendo la lista de todas las secretarias por usuario",
							e);
		} finally {
			close(con, pstmt, rst);
		}
		return secretarias;
	}

}
